﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using RFIDStockManagement.IDAL;
using RFIDStockManagement.Model.Public;
using System.Data.Common;

namespace RFIDStockManagement.DAL
{
    sealed class MeasureAccess : BasicAccess, IBasicInfoAccess
    {
        public MeasureAccess()
            : base("rfid_Measure")
        {
        }

        protected override BasicModelInfo CreateModelInfo(DbDataReader reader)
        {
            MeasureInfo info = null;
            if (reader.Read())
            {
                info = new MeasureInfo();
                int ordinal = reader.GetOrdinal("FID");
                info.Id = reader.GetInt64(ordinal);
                ordinal = reader.GetOrdinal("FName");
                info.Name = reader.GetString(ordinal);
                ordinal = reader.GetOrdinal("FNumber");
                info.Number = reader.GetString(ordinal);
                ordinal = reader.GetOrdinal("FIsBase");
                info.IsBase = reader.GetBoolean(ordinal);
                ordinal = reader.GetOrdinal("FRate");
                info.Rate = reader.GetDecimal(ordinal);
                ordinal = reader.GetOrdinal("FMeasureGroupId");
                info.MeasureGroupId = reader.GetInt64(ordinal);
            }

            return info;
        }

        /// <summary>
        /// 设置被引用信息
        /// </summary>
        /// <param name="info"></param>
        protected override void SetBeQuoteInfo(BasicModelInfo info)
        {
            DbHelper helper = new DbHelper();
            string sql = string.Format(@"SELECT TOP 1 1 FROM rfid_Item WHERE FStockMeasureID={0}
            UNION ALL
            SELECT TOP 1 1 FROM rfid_InStockbillEntry WHERE FUnitID={0}
            UNION ALL
            SELECT TOP 1 1 FROM rfid_OutStockbillEntry WHERE FUnitID={0}", info.Id); ;
            DbCommand comm = helper.GetSqlStringCommand(sql);
            object scalar = helper.ExecuteScalar(comm);
            if (scalar != null)
            {
                info.BeQuote = true;
                info.BeQuoteMessage = "已被物料或出入库单引用";
            }
        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="info"></param>
        public override BasicModelInfo Save(BasicModelInfo info)
        {
            DbHelper helper = new DbHelper();
            string sql = string.Empty;
            MeasureInfo measureInfo = info as MeasureInfo;

            sql = string.Format("SELECT TOP 1 1 FROM rfid_Measure WHERE FID<>{0} AND FNumber='{1}'", measureInfo.Id, measureInfo.Number);
            DbCommand comm = helper.GetSqlStringCommand(sql);
            object scalar = helper.ExecuteScalar(comm);
            if (scalar != null)
            {
                throw new Exception(string.Format("单位编码“{0}”已经存在！", measureInfo.Number));
            }
            
            if (measureInfo.Id <= 0)
            {
                sql = string.Format(@"INSERT INTO rfid_Measure(FName,FNumber,FIsBase,FRate,FMeasureGroupID)VALUES('{0}','{1}',{2},{3},{4});SELECT @@IDENTITY",
                    measureInfo.Name, measureInfo.Number, measureInfo.IsBase ? 1:0, measureInfo.Rate, measureInfo.MeasureGroupId);
                comm = helper.GetSqlStringCommand(sql);
                scalar = helper.ExecuteScalar(comm);
                if (scalar != null)
                {
                    long id = 0;
                    long.TryParse(scalar.ToString(), out id);
                    measureInfo.Id = id;
                }
            }
            else
            {
                sql = string.Format("UPDATE rfid_Measure SET FName='{0}',FNumber='{1}',FRate={2} WHERE FID={3}",
                    measureInfo.Name, measureInfo.Number, measureInfo.Rate, measureInfo.Id);
                comm = helper.GetSqlStringCommand(sql);
                helper.ExecuteNonQuery(comm);
            }

            return measureInfo;
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="lstInfo"></param>
        public override List<string> Delete(List<string> lstInfo)
        {
            List<string> result = new List<string>();
            string sql = string.Format(@"SELECT mg.FID,mg.FName,mg.FNumber FROM rfid_Measure mg WHERE 
mg.FID IN({0}) AND (EXISTS(SELECT TOP 1 1 FROM rfid_Item WHERE mg.FID=FStockMeasureID)
OR EXISTS(SELECT TOP 1 1 FROM rfid_InStockbillEntry WHERE mg.FID=FUnitID)
OR EXISTS(SELECT TOP 1 1 FROM rfid_OutStockbillEntry WHERE mg.FID=FUnitID))", string.Join(",", lstInfo.ToArray()));
            DbHelper helper = new DbHelper();
            DbCommand comm = helper.GetSqlStringCommand(sql);
            using (DbDataReader reader = helper.ExecuteReader(comm))
            {
                while (reader.Read())
                {
                    lstInfo.Remove(reader.GetInt64(reader.GetOrdinal("FID")).ToString());
                    result.Add(string.Format("“{0}（{1}）”已被物料或出入库单引用，不能删除", reader.GetString(reader.GetOrdinal("FName")), reader.GetString(reader.GetOrdinal("FNumber"))));
                }
            }
            if (lstInfo.Count > 0)
            {
                sql = string.Format("DELETE rfid_Measure WHERE FID IN({0})", string.Join(",", lstInfo.ToArray()));
                comm = helper.GetSqlStringCommand(sql);
                helper.ExecuteNonQuery(comm);
                if (result.Count == 0)
                {
                    result.Add("所选的数据全部删除成功");
                }
                else
                {
                    result.Add("其它所选的数据删除成功");
                }
            }
            
            return result;
        }
    }
}
